![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Oracle SNMP AgentsOracle SNMP Agents are available in Oracle version 7.3 and later. The Oracle SNMP agents allow third-party vendors to easily retrieve performance data from Oracle. These SNMP agents in conjunction with OS SNMP agents can provide a complete management solution for your system. Oracle has teamed up with other members of the System Management Tools Initiative (SMTI) to provide a shrink-wrapped solution to your RDBMS management needs. When you use a performance monitoring tool that uses the Oracle SNMP agents, you can avoid selecting information from the V$ tables directly. When you access the V$ tables, you cause some overhead within the database itself. When you use the Oracle SNMP agents, this information is retrieved in a more efficient manner (because Oracle designed it specifically for the RDBMS). The Oracle SNMP agents are designed not only to give you reliable and pertinent information, but to cause as little overhead on the system as possible. Most SMTI and other third-party vendors will soon begin collecting their information through the Oracle SNMP agents (if they have not already done so). SQL TraceOracles SQL Trace facility provides performance information about individual SQL statements. SQL Trace is activated by executing the following command: SQL> ALTER SESSION SET SQL_TRACE = TRUE; SQL Trace is turned off with this command: SQL> ALTER SESSION SET SQL_TRACE = FALSE; During the time that SQL Trace is active, performance statistics for all SQL statements generated within this session are stored in a trace file. You can then use the Oracle utility TKPROF to convert the trace file into readable form. TKPROF can also be invoked with the EXPLAIN PLAN option if you want to additionally display the execution plan. The following statistics are gathered for each SQL statement:
SQL Trace can be enabled for all users by setting the initialization parameter SQL_TRACE to TRUE. The information gathered by SQL Trace can be quite useful in debugging inefficient SQL statements, as you will see in detail in Chapter 25, Using EXPLAIN PLAN and SQL Trace. EXPLAIN PLANThe Oracle EXPLAIN PLAN command displays the execution plan chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. By examining the execution plan, you can see exactly how Oracle executes your SQL statement. The execution plan can help you determine whether you have written the an efficient SQL statement or whether changes can be made to optimize the statement. To execute an EXPLAIN PLAN statement, you must first create a table with the name plan_table and with the specified plan_table format. The table format and the SQL statement required to create the table are included in Oracle in the SQL script UTXPLAN.SQL. Once this table is created, you can execute the EXPLAIN PLAN statement by issuing these SQL statements followed by your SQL statements: EXPLAIN PLAN SET STATEMENT_ID = 'NAME' FOR In this syntax, NAME specifies a label for the statement in the plan_table table. Whether you execute it on its own or in conjunction with the SQL Trace facility, the EXPLAIN PLAN command provides useful information about how SQL statements can be optimized. OS ToolsEvery operating system has a set of tools to help you monitor the system. Although the tools themselves differ depending on whether you are running UNIX, NetWare, NetWare NT, OS/2, or some other OS, each tool gives the same basic information to the administrator. The values that the OS monitors should include the following basic parameters:
These are but a few of the important indicators you will require in the upcoming chapters. Although each operating system is different and has different monitoring tools, the basic concepts are the same.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |